﻿using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;

namespace MergeExcelCols
{
    class Program
    {


        private static void WriteExcel(List<string> allNumStrings, string fullName)
        {

            using (var package = new ExcelPackage())
            {
                //Add a new worksheet to the empty workbook
                var sheet = package.Workbook.Worksheets.Add("Sheet1");
                int rowIndex = 0;

                foreach (string s in allNumStrings)
                {
                    rowIndex++;
                    sheet.Cells[rowIndex, 1].Value = s;
                }

                sheet.Cells.AutoFitColumns(0);

                package.SaveAs(new FileInfo(fullName));
                Console.WriteLine(fullName);
            }
        }
        private static void ConvertOneFile(string excelFileName)
        {
            Console.WriteLine(excelFileName);
            FileInfo existingFile = new FileInfo(excelFileName);
            using (ExcelPackage package = new ExcelPackage(existingFile))
            {
                //Get the first worksheet in the workbook
                Regex regNums = new Regex(@"^\d{3,}$", RegexOptions.Compiled);
                List<string> allNumStrings = new List<string>();
                ExcelWorksheet worksheet = package.Workbook.Worksheets[0];
                int colCount = worksheet.Dimension.End.Column;  //get Column Count
                int rowCount = worksheet.Dimension.End.Row;     //get row count
                for (int col = 1; col <= colCount; col++)
                {
                    for (int row = 1; row <= rowCount; row++)
                    {
                        object o = worksheet.Cells[row, col].Value;
                        if (o != null)
                        {
                            string s = worksheet.Cells[row, col].Value.ToString().Trim();
                            if (regNums.IsMatch(s))
                            {
                                //Console.WriteLine(" Row:" + row + " column:" + col + " Value:" + s);
                                allNumStrings.Add(s);
                            }
                        }

                    }
                }

                string outFileName = excelFileName.Replace(".xlsx", $"_共{allNumStrings.Count}.xlsx");
                WriteExcel(allNumStrings, outFileName);


            }


        }

        static void Main(string[] args)
        {
            //if (DateTime.Now < new DateTime(2021, 8, 14) ||
            //  DateTime.Now > new DateTime(2021, 8, 17))
            //{
            //    Environment.Exit(0);
            //}
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            DirectoryInfo di = new DirectoryInfo("excels");
            if (!di.Exists)
            {
                di.Create();
            }
            Console.WriteLine("正在搜索excels/下的xlsx文件...");
            foreach (FileInfo txtFile in di.GetFiles("*.xlsx"))
            {
                if (txtFile.Name.Contains("_共"))
                    continue;
                try
                {
                    ConvertOneFile(txtFile.FullName);
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
            }
            Console.WriteLine("转换完成！");
            Console.ReadKey();
        }
    }
}
